Today, companies are under increasing pressure to provide accurate and reliable information faster and more efficiently to both internal and external decision makers. Often, the information required is financial data. However, the systems used to log transactions have traditionally been separate from the tools used to analyze the data. One example of a system used to log transactions is an online transaction processing (“OLTP”) system. An example of a system used to analyze data is an online analytical processing (“OLAP”) system.
OLTP systems manage applications that are transaction oriented, and are typically implemented using relational databases. Companies generally account for financial information using a ledger (which also may be referred to as a relational database) or other spreadsheet. These ledgers offer easy data entry and maintenance of the accounting information. However, studying the information in the ledger is cumbersome, and complex analysis is often not feasible.
A ledger may be created in a relational database to store financial transactions called “journal entries.” When a business transaction occurs, a journal entry is inserted into a table associated with the ledger. Journal entries may be inserted into a general ledger or sub-ledgers. In accounting, a general ledger (“GL”) is one of the main financial records of a business. The general ledger includes more or less all of the accounts which are reported on the financial statements, such as cash, accounts payable, and expense accounts. Each ledger in the general ledger typically keeps a balance, or the running total of money, in each account for which the business wishes to track. A sub-ledger may represent accounts payable or accounts receivable, or any other type of ledger, for example.
Each account for which journal entries are made is represented in a “chart of accounts.” A chart of accounts is a collection of account names. Small businesses may wish to track a small number of accounts, but large businesses commonly wish to track thousands or even millions of accounts. These accounts may correspond with different departments, organizations, or operational units within the business, as well as different product lines.
Although OLTP systems are simple to implement and efficient for processing transactions, generating reports from an OLTP system can be inefficient and time consuming. For example, it may be possible to determine revenues for one subsidiary over a predetermined amount of time. However, it may be very difficult or impossible to determine revenues for 15 foreign subsidiaries from selling a particular product during different times. The reason is that the data in the ledger is not stored in a manner where complex queries are easily made. Reports in OLTP systems are typically not pre-aggregated with balance information, and must therefore be executed each time the information is needed. Traditionally, complex reports generated from relational database systems are run during a prescheduled time, or from backup databases for this reason.
By contrast, OLAP systems are capable of generating complex reports in an extremely efficient manner. OLAP systems support analysis and management reporting applications, and are often implemented in a multidimensional database that stores data in “cubes.” In contrast to relational database tables, which store data in two dimensions similar to a spreadsheet, OLAP cubes can store data in many dimensions, with each dimension representing a different view of the data. For example, a financial application may include dimensions for each fiscal quarter, a dimension for each year, and a dimension for each type of currency. Once data is stored in the OLAP system, all views are automatically populated or pre-aggregated, allowing all information defined by a particular view to be accessed instantaneously. Although OLAP systems are well-suited to generate complex reports, they have limited transactional capabilities.